package com.anhuanjia.tools.eao.controller;

import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import org.springframework.util.StringUtils;

import com.anhuanjia.bean.MhBranch;
import com.anhuanjia.bean.MhResource;
import com.anhuanjia.tools.eao.bean.AhjMemberRemark;
import com.anhuanjia.tools.eao.bean.CrmCompany;
import com.anhuanjia.tools.eao.bean.EaoCompanyPhone;
import com.anhuanjia.tools.eao.bean.EaoEngineerMember;
import com.anhuanjia.tools.eao.bean.EaoIsEngineer;
import com.anhuanjia.tools.eao.bean.EaoMemberLife;
import com.anhuanjia.tools.eao.bean.EaoProjectInfo;
import com.anhuanjia.tools.eao.bean.EaoProjectStaff;
import com.anhuanjia.tools.eao.bean.EaoPublicity;
import com.anhuanjia.tools.eao.bean.EaoPublicityEnclosure;
import com.anhuanjia.tools.eao.bean.EaoPublicityVisit;
import com.anhuanjia.tools.eao.bean.EaoTof;
import com.anhuanjia.tools.eao.util.ProcedureUtils;
import com.efastsoft.bean.CrmCustomerInfo;
import com.efastsoft.bean.CrmPeople;
import com.efastsoft.bean.EcMember;
import com.efastsoft.framework.Member;
import com.efastsoft.framework.UIFacade;
import com.efastsoft.framework.annotation.Alias;
import com.efastsoft.framework.annotation.Priv;
import com.efastsoft.framework.annotation.Priv.LoginType;
import com.efastsoft.framework.collection.Mapx;
import com.efastsoft.framework.data.DataRow;
import com.efastsoft.framework.data.DataTable;
import com.efastsoft.framework.data.Q;
import com.efastsoft.framework.data.Transaction;
import com.efastsoft.framework.ui.control.CommonDataGridAction;
import com.efastsoft.framework.utility.StringUtil;
import com.efastsoft.member.bl.MemberBL;
import com.efastsoft.platform.util.NoUtil;

@Alias("NewAccountNumber")
public class NewAccountNumberUI extends UIFacade {

	/**
	 * 查询所有一级账号
	 */
	@Priv(loginType = LoginType.ALL)
	public void findLevelAccount(CommonDataGridAction cdg) {
		String fullName = $V("FullName");
		Q member = new Q(
				" select me.ID,me.Mobile,me.Type,co.FullName,co.LegalRepresentative,co.ContactName,co.ContactPhone,ml.Life from ec_member as me left join Crm_Company as co on me.ID = co.ID left join eao_member_life as ml on me.ID = ml.MemberId join ahj_member_remark as amr on amr.ID = me.ID and amr.RemarkMember like '%eao%' where me.Type='Ent' ");
		if (StringUtil.isNotEmpty(fullName)) {
			member.append(
					" and (co.FullName like ? or co.LegalRepresentative like ? or co.ContactPhone like ? or co.ContactName like ? or me.Mobile like ?) ",
					"%" + fullName + "%", "%" + fullName + "%", "%" + fullName + "%", "%" + fullName + "%",
					"%" + fullName + "%");
		}
		member.append(" order by me.AddTime desc ");
		DataTable memberEnt = member.fetch(cdg);
		cdg.bindData(memberEnt);
		cdg.setTotal(member);

	}

	/**
	 * 人员添加
	 */
	@Priv(loginType = LoginType.ALL)
	public void addMember() {
		Transaction transaction = new Transaction();
		String Mobile = $V("Mobile");
		String FullName = $V("UserName");
		String Type = $V("Type");// 0更新1新增
		AhjMemberRemark me = new AhjMemberRemark();
		if (StringUtil.isNotEmpty(Mobile)) {
			DataTable dt = new Q(" SELECT * FROM Ec_Member where  Mobile = ? ", Mobile).fetch();
			if (dt.getRowCount() != 0) {
				EcMember em = new EcMember();
				em.setValue(dt.get(0));
				if (em.getType().equals("Ent")) {
					s("该账号是已有一级账号");
				} else {
					DataTable dtMember = new Q(" SELECT * FROM mh_resource where  Member = ? ", em.getID()).fetch();
					if (dtMember.getRowCount() > 0) {
						s("本账号为系统已有的二级账号，从系统内剔除，才能增添为一级机构账号");
					} else {
						s("本账号为游客账号，是否更改为一级机构账号?");
					}
				}
			} else {
				s("该账号不存在请添加");
			}
			if ("0".equals(Type)) {
				Mapx<String, Object> mdata = new Mapx<String, Object>();
				mdata.put("SiteID", 1);
				mdata.put("Status", "Y");
				mdata.put("Mobile", Mobile);
				mdata.put("Type", "Ent");
				mdata.put("Source", "BA");
				mdata.put("AddTime", new Date());
				EcMember member = MemberBL.update(dt.get(0).getString("UserName"), mdata, transaction);
				me.setID(member.getID());
				
				CrmPeople people = new CrmPeople();
				people.setID(member.getID());
				if (people.fill()) {
					people.setFullName(member.getRealName());
					people.setMobile(member.getMobile());
					people.setAddTime(new Date());
					transaction.update(people);
				} else {
					people.setFullName(member.getRealName());
					people.setMobile(member.getMobile());
					people.setAddTime(new Date());
					transaction.insert(people);
				}

				CrmCompany company = new CrmCompany();
				company.setID(member.getID());
				if (company.fill()) {
					company.setFullName(FullName);
					company.setVerified("1");
					company.setContactPhone(Mobile);
					transaction.update(company);
				} else {
					company.setFullName(FullName);
					company.setVerified("1");
					company.setContactPhone(Mobile);
					transaction.insert(company);
				}

				MhBranch branch = new MhBranch();
				branch.setValue(this.Request);
				branch.setSiteID(0L);
				branch.setCompany(member.getID());
				branch.setID(NoUtil.getMaxID("MhBranchID"));
				branch.setBranchInnerCode(NoUtil.getMaxNo("MBICode_" + branch.getCompany(), 4));
				branch.setParentInnerCode("0");
				branch.setTreeLevel(1L);
				branch.setName(FullName);
				branch.setCompanyType("0");
				branch.setIsLeaf("Y");
				dt = (new Q("select * from Mh_Branch where Company=? order by Orderflag",
						new Object[] { branch.getCompany() })).fetch();
				String orderflag = "";
				if (dt != null && dt.getRowCount() > 0) {
					orderflag = dt.getString(dt.getRowCount() - 1, "OrderFlag");
				} else {
					orderflag = "0";
				}
				transaction.add(new Q("update Mh_Branch set Orderflag=Orderflag+1 where Company=? and Orderflag>?",
						new Object[] { branch.getCompany(), orderflag }));
				branch.setOrderFlag(orderflag + 1);
				branch.setAddTime(new Date());
				branch.setAddUser(Member.getUID());

				EaoTof tof = new EaoTof();
				tof.setBranchId(branch.getBranchInnerCode());
				tof.setCompanyId(member.getID().toString());
				tof.setTof("0");
				
				if (me.fill()) {
					// 修改
					if (StringUtil.isNotEmpty(me.getRemarkMember())) {
						if (!me.getRemarkMember().contains("eao")) {
							me.setRemarkMember(me.getRemarkMember().trim() + "," + "eao");
						}
					} else {
						me.setRemarkMember("eao");
					}
					me.setModifyTime(new Date());
					transaction.update(me);
				} else {
					// 新增
					me.setRemarkMember("eao");
					me.setAddTime(new Date());
					transaction.insert(me);
				}

				transaction.insert(tof);
				transaction.insert(branch);

				// 根据Member id查询Resource是否存在
				DataTable resource = new Q(" select * from Mh_Resource where Member =  ? ", member.getID()).fetch();
				MhResource mhResource = new MhResource();
				if (resource.getRowCount() != 0) {
					for (DataRow dataRow : resource) {
						mhResource.setValue(dataRow);
						transaction.delete(mhResource);
					}
				}
				// 根据Resource查询是否存在职位
				DataTable exists = new Q(
						" select * from Mh_Resource as re,eao_is_engineer as ie, eao_Position as po where re.Member = ie.Member and ie.IsEngineer = po.id and re.Member =  ? ",
						mhResource.getMember()).fetch();
				if (exists.getRowCount() != 0) {
					// 删除关系表
					Q deleteExists = new Q(" DELETE FROM eao_is_engineer WHERE Member = ?", mhResource.getMember());
					transaction.add(deleteExists);
				}
				
				if (transaction.commit()) {
					s("一级账号创建成功");
				} else {
					f("一级账号创建失败");
				}
			} else if ("1".equals(Type)) {
				if (StringUtil.isNotEmpty(FullName)) {
					Mapx<String, Object> mdata = new Mapx<String, Object>();
					mdata.put("Password", "123456");
					mdata.put("SiteID", 1);
					mdata.put("Status", "Y");
					mdata.put("Mobile", Mobile);
					mdata.put("Type", "Ent");
					mdata.put("Source", "BA");
					mdata.put("AddTime", new Date());

					EcMember member = MemberBL.insert(UUID.randomUUID().toString().replace("-", ""), mdata,
							transaction);
					me.setID(member.getID());
					
					CrmPeople people = new CrmPeople();
					people.setID(member.getID());
					if (people.fill()) {
						people.setFullName(member.getRealName());
						people.setMobile(member.getMobile());
						people.setAddTime(new Date());
						transaction.update(people);
					} else {
						people.setFullName(member.getRealName());
						people.setMobile(member.getMobile());
						people.setAddTime(new Date());
						transaction.insert(people);
					}

					CrmCustomerInfo info = new CrmCustomerInfo();
					info.setID(member.getID());
					if (info.fill()) {
						info.setFullName(FullName);
						info.setCustType(member.getType());
						info.setVerified(0);
						info.setModifyTime(new Date());
						transaction.update(info);
					} else {
						info.setFullName(FullName);
						info.setCustType(member.getType());
						info.setVerified(0);
						info.setAddTime(new Date());
						transaction.insert(info);
					}

					CrmCompany com = new CrmCompany();
					com.setID(member.getID());
					if (com.fill()) {
						// 更新
						com.setFullName(FullName);
						com.setContactPhone(Mobile);
						com.setVerified("1");
						com.setModifyTime(new Date());
						transaction.update(com);
					} else {
						// 添加
						com.setFullName(FullName);
						com.setContactPhone(Mobile);
						com.setVerified("1");
						com.setAddTime(new Date());
						transaction.insert(com);
					}

					MhBranch branch = new MhBranch();
					branch.setValue(this.Request);
					branch.setSiteID(0L);
					branch.setCompany(member.getID());
					branch.setID(NoUtil.getMaxID("MhBranchID"));
					branch.setBranchInnerCode(NoUtil.getMaxNo("MBICode_" + branch.getCompany(), 4));
					branch.setParentInnerCode("0");
					branch.setTreeLevel(1L);
					branch.setName(FullName);
					branch.setCompanyType("0");
					branch.setIsLeaf("Y");
					branch.setType("0");
					dt = (new Q("select * from Mh_Branch where Company=? order by Orderflag",
							new Object[] { branch.getCompany() })).fetch();
					String orderflag = "";
					if (dt != null && dt.getRowCount() > 0) {
						orderflag = dt.getString(dt.getRowCount() - 1, "OrderFlag");
					} else {
						orderflag = "0";
					}
					transaction.add(new Q("update Mh_Branch set Orderflag=Orderflag+1 where Company=? and Orderflag>?",
							new Object[] { branch.getCompany(), orderflag }));
					branch.setOrderFlag(orderflag + 1);
					branch.setAddTime(new Date());
					branch.setAddUser(Member.getUID());

					EaoTof tof = new EaoTof();
					tof.setBranchId(branch.getBranchInnerCode());
					tof.setCompanyId(member.getID().toString());
					tof.setTof("0");
					
					if (me.fill()) {
						// 修改
						if (StringUtil.isNotEmpty(me.getRemarkMember())) {
							if (!me.getRemarkMember().contains("eao")) {
								me.setRemarkMember(me.getRemarkMember().trim() + "," + "eao");
							}
						} else {
							me.setRemarkMember("eao");
						}
						me.setModifyTime(new Date());
						transaction.update(me);
					} else {
						// 新增
						me.setRemarkMember("eao");
						me.setAddTime(new Date());
						transaction.insert(me);
					}
					
					transaction.insert(branch);
					transaction.insert(tof);
					if (transaction.commit()) {
						s("一级账号创建成功");
					} else {
						f("一级账号创建失败");
					}
				} else {
					f("请输入机构名称");
				}
			}
			
			
		}
	}

	/**
	 * 根据id查询账号
	 */
	@Priv(loginType = LoginType.ALL)
	public void findOneAccount() {
		String id = $V("ID");
		if(StringUtils.isEmpty(id)) {
			f("操作失败");
		}
		DataTable dt = new Q(
				" select me.ID,me.Mobile,me.Gender,co.ContactPhone,co.ContactName,me.UserName,me.AddTime,ml.Life,ml.CountMember,co.FullName,co.LegalRepresentative,co.Address,co.LicenseNumber,cp.Id as OtherPhoneId,cp.SecondPhone,cp.ThirdPhone from ec_member as me left join Crm_Company as co on me.ID=co.ID left join eao_member_life as ml on me.ID= ml.MemberId left join eao_company_phone cp on cp.Company = me.ID where me.ID=? ",
				id).fetch();
		if (dt.getRowCount() > 0) {
			DataRow dr = dt.get(0);
			this.Response.putAll(dr.toMapx());
		}
	}

	/**
	 * 根据id修改机构信息
	 * 
	 * @throws ParseException
	 */
	@Priv(loginType = LoginType.ALL)
	public void updatePublicity() throws ParseException {
		Transaction trans = new Transaction();
		String id = $V("ID");
		if (StringUtil.isNotEmpty(id)) {
			String gender = $V("Gender");
			String fullName = $V("FullName");
			String legalRepresentative = $V("LegalRepresentative");
			String LicenseNumber = $V("LicenseNumber");
			String Address = $V("Address");
			String ContactPhone = $V("ContactPhone");
			String ContactName = $V("ContactName");
			String life = $V("Life");
			String CountMember = $V("CountMember");
			// 机构额外联系方式
			String OtherPhoneId = $V("OtherPhoneId");
			String SecondPhone = $V("SecondPhone");
			String ThirdPhone = $V("ThirdPhone");

			if (StringUtil.isNotEmpty(gender)) {
				Q member = new Q(" UPDATE ec_member SET Gender = ?", gender);
				if (StringUtil.isNotEmpty(fullName)) {
					member.append(" , RealName = ?", fullName);
				}
				CrmPeople people = new CrmPeople();
				people.setID(id);
				if (people.fill()) {
					people.setGender(gender);
					trans.update(people);
				} else {

					people.setGender(gender);
					trans.insert(people);
				}
				member.append(" where Id = ? ", id);
				trans.add(member);
			} else if (StringUtil.isNotEmpty(fullName)) {
				Q member = new Q(" UPDATE ec_member SET RealName = ? where Id = ?", fullName, id);
				trans.add(member);
			}
			Q company = new Q(" update crm_company SET  ");
			if (StringUtil.isNotEmpty(fullName)) {
				company.append("FullName = ? ", fullName);
				if (StringUtil.isNotEmpty(legalRepresentative) || StringUtil.isNotEmpty(LicenseNumber)
						|| StringUtil.isNotEmpty(Address) || StringUtil.isNotEmpty(ContactPhone)) {
					company.append(" , ");
				}
			}
			if (StringUtil.isNotEmpty(legalRepresentative)) {
				company.append("LegalRepresentative = ? ", legalRepresentative);
				if (StringUtil.isNotEmpty(LicenseNumber) || StringUtil.isNotEmpty(Address)
						|| StringUtil.isNotEmpty(ContactPhone)) {
					company.append(" , ");
				}
			}
			if (StringUtil.isNotEmpty(LicenseNumber)) {
				company.append("LicenseNumber = ? ", LicenseNumber);
				if (StringUtil.isNotEmpty(Address) || StringUtil.isNotEmpty(ContactPhone)) {
					company.append(" , ");
				}
			}
			if (StringUtil.isNotEmpty(Address)) {
				company.append("Address = ? ", Address);
				if (StringUtil.isNotEmpty(ContactPhone)) {
					company.append(" , ");
				}
			}
			if (StringUtil.isNotEmpty(ContactPhone)) {
				company.append("ContactPhone = ? ", ContactPhone);
				if (StringUtil.isNotEmpty(ContactName)) {
					company.append(" , ");
				}
			}
			if (StringUtil.isNotEmpty(ContactName)) {
				company.append("ContactName = ? ", ContactName);
			}
			company.append(" where ID = ? ", id);
			trans.add(company);
			DataTable dataTable = new Q(" select * from eao_member_life where MemberId = ?", id).fetch();
			if (dataTable.getRowCount() != 0) {
				Q memberLife = new Q(" update eao_member_life SET ");
				if (StringUtil.isNotEmpty(life)) {
					memberLife.append("Life = ? ,", life);
				}
				if (StringUtil.isNotEmpty(CountMember)) {
					memberLife.append("CountMember = ? ,", CountMember);
				}
				memberLife.append("  UpdateTime =? where MemberId=? ", new Date(), id);
				trans.add(memberLife);
			} else {
				EaoMemberLife memberLife = new EaoMemberLife();
				memberLife.setLife(new SimpleDateFormat("yy-MM-dd").parse(life));
				memberLife.setMemberId(Long.parseLong(id));
				memberLife.setId(UUID.randomUUID().toString());
				memberLife.setCountMember(CountMember);
				memberLife.setCreateTime(new Date());
				memberLife.setUpdateTime(new Date());
				trans.insert(memberLife);
			}

			if (StringUtil.isNotEmpty(OtherPhoneId)) {
				EaoCompanyPhone cp = new EaoCompanyPhone();
				cp.setId(OtherPhoneId);
				cp.setSecondPhone(SecondPhone);
				cp.setThirdPhone(ThirdPhone);
				cp.setCompany(Long.valueOf(id));
				trans.update(cp);
			} else {
				EaoCompanyPhone cp = new EaoCompanyPhone();
				cp.setId(UUID.randomUUID().toString().replace("-", ""));
				cp.setSecondPhone(SecondPhone);
				cp.setThirdPhone(ThirdPhone);
				cp.setCompany(Long.valueOf(id));
				trans.insert(cp);
			}

			if (trans.commit()) {
				s("信息修改成功");
			} else {
				f("信息修改失败");
			}
		} else {
			f("传入的ID为空");
		}
	}

	/**
	 * 根据id删除用户
	 */
	@Priv(loginType = LoginType.ALL)
	public void deleteEntId() {
		String str = $V("Id");
		String Mobile = $V("Mobile");
		if (StringUtils.isEmpty(str)) {
			if (StringUtil.isNotEmpty(Mobile)) {
				DataTable dtM = new Q(" SELECT * FROM ec_member where  Mobile = ? and type = 'Ent'", Mobile).fetch();
				if (dtM.getRowCount() != 0) {
					str = dtM.getString(0, "ID");
				} else {
					f("请输入正确的一级账号手机号");
					return;
				}
			} else {
				f("请输入正确的手机号");
				return;
			}
		}
		Transaction transaction = new Transaction();
		if(StringUtils.isEmpty(str)) {
			f("操作失败");
		}
		String[] idList = str.split(",");
		for (String id : idList) {
			DataTable dt = new Q("select * from crm_company where ID = ?", id).fetch();
			DataTable dt2 = new Q("select * from mh_branch where Company = ?", id).fetch();
			DataTable dt3 = new Q("select * from ec_member where ID = ?", id).fetch();
			DataTable dt4 = new Q("select * from mh_resource where Company = ?", id).fetch();
			if (dt.getRowCount() != 0) {
				CrmCompany entity = new CrmCompany();
				entity.setValue(dt.get(0));
				transaction.delete(entity);
			}
			if (dt4.getRowCount() != 0) {
				for (DataRow string : dt4) {
					MhResource entity = new MhResource();
					entity.setValue(string);
					transaction.delete(entity);
				}
			}
			if (dt3.getRowCount() != 0) {
				EcMember entity = new EcMember();
				entity.setValue(dt3.get(0));
				transaction.delete(entity);
			}
			if (dt2.getRowCount() != 0) {
				for (DataRow string : dt2) {
					MhBranch entity = new MhBranch();
					entity.setValue(string);
					transaction.delete(entity);
				}
			}

		}
		if (transaction.commit()) {
			s("一级账号删除成功");
		} else {
			f("一级账号删除失败");
		}
	}

	/**
	 * 模糊查询所有公示、未审核公示
	 */
	@Priv(loginType = LoginType.ALL)
	public void findLikePublicity(CommonDataGridAction cdg) {
		// 模糊查询查询所有公示
		String title = $V("Title");
		String State = $V("State");
		String StartTime = $V("StartTime");
		String EndTime = $V("EndTime");

		Q q = new Q(
				" select p.Id,co.FullName,coalesce(mh.Tel,mh.Mobile) as Tel,p.Title,p.State,p.IsDelete,mh.Mobile,mh.RealName,p.PublicityTime as PublicityTime,em.Mobile CompanyMobile  from eao_publicity p left join Ec_Member as mh on mh.Id = p.Operator left join mh_resource as mr on mr.Member = p.Operator left join crm_company as co on (mh.ID = co.ID or mr.Company = co.ID) left join Ec_Member as em on co.ID = em.ID where  em.Type = 'Ent'");

		if (StringUtil.isNotEmpty(title)) {
			if ("非体系内账号".equals(title)) {
				q.append(
						" and (p.Operator not in (select Member from mh_resource ) and p.Operator not in(select cc.ID from crm_company cc left join ec_member em on cc.ID = em.ID where em.Type = 'Ent' ))");
			} else {
				q.append(
						" and (p.Title like ? or em.Mobile like ? or co.FullName like ? or mh.Mobile like ? or mh.RealName like ? )",
						"%" + title + "%", "%" + title + "%", "%" + title + "%", "%" + title + "%", "%" + title + "%");
			}
		}
		if (StringUtil.isNotEmpty(State)) {
			q.and().eq("p.State", State);
		}
		if (StringUtil.isNotEmpty(StartTime)) {
			q.and().append(" DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')>=?", StartTime);
		}
		if (StringUtil.isNotEmpty(EndTime)) {
			q.and().append(" DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')<=?", EndTime);
		}
		q.append(" order by p.PublicityTime desc");

		DataTable dt = q.fetch(cdg);
		for (DataRow dataRow : dt) {
			if (StringUtil.isEmpty(dataRow.getString("CompanyMobile"))) {
				dataRow.set("FullName", "非体系内账号");
				dataRow.set("CompanyMobile", "非体系内账号");
			}
		}
		cdg.bindData(dt);
		cdg.setTotal(q);

	}

	/**
	 * 根据id删除多条公示
	 */
	@Priv(loginType = LoginType.ALL)
	public void deletePublicityId() {
		String str = $V("Id");
		Transaction transaction = new Transaction();
		String[] idList = str.split(",");
		for (String id : idList) {
			DataTable dt = new Q("select * from eao_publicity where Id = ?", id).fetch();
			EaoPublicity publicity = new EaoPublicity();
			publicity.setValue(dt.get(0));
			publicity.setIsDelete("1");
			publicity.setState("0");
			transaction.update(publicity);
		}
		if (transaction.commit()) {
			s("系统管理员公示删除成功");
		} else {
			f("系统管理员公示删除失败");
		}
	}

	/**
	 * 根据id查询公示
	 * 
	 * @throws UnsupportedEncodingException
	 */
	@Priv(login = false)
	public void findByPublicityId() throws UnsupportedEncodingException {
		String Id = $V("Id");
		if(StringUtils.isEmpty(Id)) {
			f("操作失败");
		}
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		Transaction transaction = new Transaction();
		DataTable dt = new Q("select * from eao_publicity where Id = ?", Id).fetch();
		EaoPublicity publicity = new EaoPublicity();
		if (dt.getRowCount() != 0) {
			publicity.setValue(dt.get(0));
			Integer i = publicity.getVisitNumber();
			if (null == i) {
				i = 0;
			}
			publicity.setVisitNumber(i + 1);
			transaction.update(publicity);
		}
		DataTable dataTable = new Q("select * from eao_publicity_enclosure where PublicityId = ? ",
				publicity.getFileID()).fetch();
		dataTable.insertColumn("Name");
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if (dataTable.getRowCount() != 0) {
			for (DataRow dataRow : dataTable) {
				Map<String, Object> map = new HashMap<>();
				EaoPublicityEnclosure publicityEnclosure = new EaoPublicityEnclosure();
				publicityEnclosure.setValue(dataRow);
				String[] str = publicityEnclosure.getEnclosure().split("/");
				String strName = str[str.length - 1];
				String[] stringName = strName.split("\\?");
				String name = stringName[0].substring(5);
				String[] nameList = name.split("\\.");
				String decodeName = java.net.URLDecoder.decode(nameList[0], "UTF-8");
				String fileName = decodeName + "." + nameList[1];
				dataRow.set("Name", fileName);
				list.add(map);
			}
		}
		// 日访问量
		try {
			Date date = new Date();
			String str = sf.format(date);
			Date visit_time = sf.parse(str);
			DataTable table = new Q("select * from eao_publicity_visit where VisitTime = ?", visit_time).fetch();
			EaoPublicityVisit publicityVisit = new EaoPublicityVisit();
			if (table.getRowCount() > 0) {
				publicityVisit.setValue(table.get(0));
				Integer Visit = publicityVisit.getVisitNumber();
				publicityVisit.setVisitNumber(Visit + 1);
				transaction.update(publicityVisit);
			} else {
				publicityVisit.setId(UUID.randomUUID().toString());
				publicityVisit.setVisitTime(visit_time);
				publicityVisit.setVisitNumber(1);
				transaction.insert(publicityVisit);
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		transaction.commit();
		boolean haveEnclosure = false;
		if (dataTable.getRowCount() > 0) {
			haveEnclosure = true;
		}
		this.Response.put("publicity", publicity);
		this.Response.put("publicityEnclosure", dataTable);
		this.Response.put("haveEnclosure", haveEnclosure);
	}

	/**
	 * 文件删除
	 */
	@Priv(loginType = LoginType.ALL)
	public void delete() {
		String Id = $V("Id");
		if(StringUtils.isEmpty(Id)) {
			f("操作失败");
		}
		DataTable dataTable = new Q(" select * from eao_publicity_enclosure where Id = ? ", Id).fetch();
		if (dataTable.getRowCount() != 0) {
			EaoPublicityEnclosure enclosure = new EaoPublicityEnclosure();
			enclosure.setValue(dataTable.get(0));
			if (enclosure.delete()) {
				s("文件删除成功");
			} else {
				f("文件删除失败");
			}
		} else {
			f("请选择你要删除的文件");
		}
	}

	/**
	 * 模糊查询查询所有未审核公示
	 */
	@Priv(loginType = LoginType.ALL)
	public void findAuditPublicity(CommonDataGridAction cdg) {
		String title = $V("Title");
		String StartTime = $V("StartTime");
		String EndTime = $V("EndTime");
		Q noQ = new Q(
				" select p.Id,co.FullName,coalesce(mh.Tel,mh.Mobile) as Tel,p.Title,p.State,p.IsDelete,mh.Mobile,mh.RealName,p.PublicityTime as PublicityTime,em.Mobile CompanyMobile  from eao_publicity p left join Ec_Member as mh on mh.Id = p.Operator left join mh_resource as mr on mr.Member = p.Operator left join crm_company as co on mh.ID = co.ID or mr.Company = co.ID left join Ec_Member as em on co.ID = em.ID where p.State = 1 and p.IsDelete = 0 and em.Type = 'Ent' ");
		if (StringUtil.isNotEmpty(title)) {
			if ("非体系内账号".equals(title)) {
				noQ.append(
						" and (p.Operator not in (select Member from mh_resource ) and p.Operator not in(select cc.ID from crm_company cc left join ec_member em on cc.ID = em.ID where em.Type = 'Ent' ))");
			} else {
				noQ.append(
						" and (p.Title like ? or em.Mobile like ? or co.FullName like ? or mh.Mobile like ? or mh.RealName like ? )",
						"%" + title + "%", "%" + title + "%", "%" + title + "%", "%" + title + "%", "%" + title + "%");
			}
		}
		if (StringUtil.isNotEmpty(StartTime)) {
			noQ.and().append(" DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')>=?", StartTime);
		}
		if (StringUtil.isNotEmpty(EndTime)) {
			noQ.and().append(" DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')<=?", EndTime);
		}
		noQ.append(" order by p.PublicityTime desc");
		DataTable noDt = noQ.fetch(cdg);
		for (DataRow dataRow : noDt) {
			if (StringUtil.isEmpty(dataRow.getString("CompanyMobile"))) {
				dataRow.set("FullName", "非体系内账号");
				dataRow.set("CompanyMobile", "非体系内账号");
			}
		}
		cdg.bindData(noDt);
		cdg.setTotal(noQ);
	}

	/**
	 * 审核公示
	 */
	@Priv(loginType = LoginType.ALL)
	public void AuditPublicity() {
		String Id = $V("Id");
		String State = $V("State");
		if(StringUtils.isEmpty(Id)) {
			f("操作失败");
		}
		DataTable dt = new Q("select * from eao_publicity where Id = ?", Id).fetch();
		if (dt.getRowCount() != 0) {
			Transaction transaction = new Transaction();
			EaoPublicity publicity = new EaoPublicity();
			publicity.setValue(dt.get(0));
			publicity.setState(State);
			transaction.update(publicity);
			if (transaction.commit()) {
				if ("2".equals(State)) {
					s("公示审核通过");
				} else if ("0".equals(State)) {
					f("公示审核未通过");
				}
			} else {
				f("公示审核失败");
			}
		}
	}

	/**
	 * 半年内所有公示（发布记录）
	 */
	@Priv(loginType = LoginType.ALL)
	public void findPublicityWithTime(CommonDataGridAction cdg) {
		// 模糊查询查询所有公示
		Q q = new Q(
				"select p.Id,p.Title,mh.RealName,p.PublicityTime from eao_publicity as p,Ec_Member as mh where p.IsDelete = 0 and p.State = 2 and mh.Id = p.Operator");
		// 查询半年内所有人发布成功的公示
		q.append(" and date_sub(curdate(), interval 6 month) <= date(p.PublicityTime) order by p.PublicityTime desc ");
		DataTable dt = q.fetch();
		Q qNum = new Q(
				"select p.Id,p.Title,mh.RealName,p.PublicityTime from eao_publicity as p,Ec_Member as mh where p.IsDelete = 0 and p.State = 2 and mh.Id = p.Operator");
		qNum.append(
				" and date_sub(curdate(), interval 6 month) <= date(p.PublicityTime) order by p.PublicityTime desc ");
		cdg.bindData(dt);
		cdg.setTotal(qNum);
	}

	@Priv(loginType = LoginType.ALL)
	public void findSixMonthSub(CommonDataGridAction dga) {
		String sortField = $V("sortField");
		String sortOrder = $V("sortOrder");
		String SearchText = $V("SearchText");

		Q q = new Q(
				"select mr.Member,mr.Company,em.Mobile,em.RealName,coalesce(em.Tel,em.Mobile) Tel,mr.AddTime from Mh_Resource mr LEFT JOIN Ec_Member em on mr.Member = em.ID join ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%'  where 1 = 1");
		if (StringUtil.isNotEmpty(SearchText)) {
			q.and().like("em.Mobile", "%" + SearchText + "%");
			q.or().like("em.RealName", "%" + SearchText + "%");
			q.or().append(
					" mr.Company in (select cc.ID from crm_company cc LEFT JOIN ec_member em on cc.ID = em.ID where cc.FullName like ? or em.Mobile like ?)",
					"%" + SearchText + "%", "%" + SearchText + "%");
		}

		Set<Long> ids = new HashSet<>();
		if (StringUtil.isNotEmpty(sortField) && StringUtil.isNotEmpty(sortOrder)) {
			q.orderby(sortField + " " + sortOrder);
		} else {
			q.orderby("mr.AddTime desc");
		}
		DataTable dataTable = q.fetch(dga);
		for (DataRow dataRow : dataTable) {
			ids.add(dataRow.getLong("Company"));
		}
		if (dataTable.getRowCount() > 0) {
			dataTable.insertColumn("CompanyMobile");
			dataTable.insertColumn("CompanyName");

			DataTable dt = new Q(
					"select em.ID,em.Mobile,cc.FullName from Ec_Member em left join crm_company cc on em.ID=cc.ID")
							.where().in("em.ID", ids).fetch();
			for (DataRow dataRow : dataTable) {
				for (DataRow dtRow : dt) {
					if (dataRow.getLong("Company") == dtRow.getLong("ID")) {
						dataRow.set("CompanyName", dtRow.get("FullName"));
						dataRow.set("CompanyMobile", dtRow.get("Mobile"));
					}
				}
			}
			dga.bindData(dataTable);
			dga.setTotal(q);
		} else {
			$F("没有数据");
		}
	}

	/**
	 * 查询所有二级账号
	 */
	@Priv(loginType = LoginType.ALL)
	public void findSubAccount(CommonDataGridAction dga) {
		String realName = $V("RealName");
		String CompanyInfo = $V("CompanyInfo");
		Q q = new Q(
				"select em.Gender,em.Mobile,mb.Name,em.RealName,mr.Company,cc.FullName as EmployeeNumber,mr.AddTime,mr.Member,ep.Type,Eie.IsEngineer,coalesce(ep.Position,'游客') as Position  "
						+ " from Mh_Resource mr LEFT JOIN ec_member em on mr.Member = em.ID"
						+ " LEFT JOIN mh_branch mb on mr.BranchInnerCode = mb.BranchInnerCode and mr.Company = mb.Company "
						+ " LEFT JOIN crm_company cc on mr.Company = cc.ID"
						+ " LEFT JOIN eao_is_engineer eie on eie.Member = mr.Member"
						+ " LEFT JOIN eao_Position as ep on ep.Id= eie.IsEngineer "
						+ " JOIN ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%' "
						+ " where mr.Company in(select cc.ID from crm_company cc left join ec_member em on cc.ID = em.ID where 1 = 1");

		if (StringUtil.isNotEmpty(CompanyInfo)) {
			q.append(" and (cc.FullName like ? or em.Mobile like ?)) ", "%" + CompanyInfo + "%",
					"%" + CompanyInfo + "%");
		} else {
			q.append(")");
		}
		if (StringUtil.isNotEmpty(realName)) {
			q.append(" and (em.RealName like ? or em.Mobile like ?) ", "%" + realName + "%", "%" + realName + "%");
		}

		q.append(" order by AddTime desc");
		DataTable dataTable = q.fetch(dga);
		dataTable.insertColumn("CompanyMobile");
		DataTable CompanyInfoTemp = new Q(
				"select em.ID,em.Mobile CompanyMobile from crm_company cc left join ec_member em on cc.ID = em.ID")
						.fetch();
		for (DataRow companyInfo : CompanyInfoTemp) {
			for (DataRow resourceInfo : dataTable) {
				if (resourceInfo.getLong("Company") == companyInfo.getLong("ID")) {
					resourceInfo.set("CompanyMobile", companyInfo.getString("CompanyMobile"));
				}
			}
		}
		if (dataTable.getRowCount() != 0) {
			dga.bindData(dataTable);
			dga.setTotal(q);
		} else {
			$F("暂无二级个人账户");
		}
	}

	/**
	 * 删除二级人员
	 */
	@Priv(loginType = LoginType.ALL)
	public void deleteInformation() {
		String memberId = $V("MemberId");
		String Company = $V("Company");
		if(StringUtils.isEmpty(memberId) && StringUtils.isEmpty(Company)) {
			f("操作失败");
		}
		String name = new Q(" select RealName from Ec_Member where Id = ? ", memberId).executeString();
		String deleteStr = "删除&nbsp;&nbsp" + name + "&nbsp;&nbsp后将影响到某些项目流程，以下环节重新进行人员分配后才能删除此人：" + name + "</br>";
		String deleteStr5 = name + "为机构唯一档案员，删除&nbsp;&nbsp" + name + "&nbsp;&nbsp后将影响到某些项目流程，分配新的档案员后才能删除此人：" + name
				+ "</br>";
		Transaction trans = new Transaction();
		if (StringUtil.isNotEmpty(memberId)) {
//			档案员
			DataTable position = new Q(
					" select Id , ParentId from eao_Position where Type = ? and Id = (select IsEngineer from eao_is_engineer where Member = ?) ",
					Company, memberId).fetch();
			if (position.getRowCount() != 0 && (position.getInt(0, 0) == 7 || position.getInt(0, 1) == 7)) {
				Integer positionId = new Q(" select Id from eao_Position where Type = ? and ParentId = 7 ",
						Company).executeInt();
				if (positionId == 0) {
					int count = new Q(
							" select count(*) from eao_is_engineer as ei join mh_resource as mr on ei.Member = mr.Member and mr.Company = ? where IsEngineer = 7 ",
							Company).executeInt();
					DataTable dataTable = new Q(" select * from eao_project_info where PerjectType <> 16").fetch();
					if (count == 1 && dataTable.getRowCount() != 0) {
						f(deleteStr5);
						return;
					}
				} else {
					int count = new Q(
							" select count(*) from eao_is_engineer as ei join mh_resource as mr on ei.Member = mr.Member and mr.Company = ? where IsEngineer = ? ",
							Company, positionId).executeInt();
					DataTable dataTable = new Q(" select * from eao_project_info where PerjectType <> 16").fetch();
					if (count == 1 && dataTable.getRowCount() != 0) {
						f(deleteStr5);
						return;
					}
				}
			}

			// 项目组成员/负责人
			DataTable Table = new Q(
					" select ProjectId from eao_project_staff where 1 = 1 and ReportChapter is null and StaffName = ?",
					memberId).fetch();
			List<String> listStr = new ArrayList<>();
			List<String> listProjectId = new ArrayList<>();
			if (Table.getRowCount() != 0) {
				for (DataRow dataRow : Table) {
					listStr.add(dataRow.getString(0));
				}
			}

			DataTable projectDt = null;
			Q leader = new Q(
					" select * from eao_project_info where (PerjectType <> 16) and PerjectLeader = " + memberId + " ");
			if (Table.getRowCount() > 0) {
				Q q = new Q(" select * from eao_project_info where (PerjectType <> 16 or PerjectType > 2) ");
				q.and().in("id ", listStr);
				projectDt = q.fetch();
			}
			String staffProject = null;
			if (projectDt != null && projectDt.getRowCount() != 0) {
				for (DataRow dataRow : projectDt) {
					EaoProjectInfo projectInfo = new EaoProjectInfo();
					projectInfo.setValue(dataRow);
					listProjectId.add(projectInfo.getId());
					if (StringUtils.isEmpty(staffProject)) {
						staffProject = projectInfo.getProjectName();
					} else {
						staffProject = staffProject + "," + projectInfo.getProjectName();
					}
				}
			}
			DataTable projectleader = leader.fetch();
			String leaderProject = null;
			if (projectDt != null && projectDt.getRowCount() != 0) {
				for (DataRow dataRow : projectleader) {
					EaoProjectInfo projectInfo = new EaoProjectInfo();
					projectInfo.setValue(dataRow);
					listProjectId.add(projectInfo.getId());
					if (StringUtils.isEmpty(leaderProject)) {
						leaderProject = projectInfo.getProjectName();
					} else {
						leaderProject = staffProject + "," + projectInfo.getProjectName();
					}
				}
			}

//			项目审核人
			DataTable auditer = new Q(
					" select pi.ProjectName , pi.PerjectType , a.AuditType from eao_project_info as pi join eao_audit as a on a.ProjectId = pi.Id where Auditor = ? ",
					memberId).fetch();
			String auditProject = null;
			for (DataRow dataRow : auditer) {
				String auditType = dataRow.getString("AuditType");
				int perjectType = dataRow.getInt("PerjectType");
				int auditT = 0;
				switch (auditType) {
				case "一审":
					auditT = 6;
					break;
				case "二审":
					auditT = 7;
					break;
				case "初审":
					auditT = 8;
					break;
				case "复审":
					auditT = 9;
					break;
				case "终审":
					auditT = 10;
					break;
				}
				if (auditT >= perjectType) {
					if (StringUtils.isEmpty(auditProject)) {
						auditProject = dataRow.getString("ProjectName");
					} else {
						auditProject = auditProject + "," + dataRow.getString("ProjectName");
					}
				}
			}

			String deleteStr1 = "项目负责人：" + leaderProject + "</br>";
			String deleteStr2 = "项目组成员：" + staffProject + "</br>";
			String deleteStr3 = "审核人员：" + auditProject + "</br>";
//			String deleteStr4 = "<p>章节分配：（提示项目名称，前后项目名称相隔两个空格，仅其参加了章节分配才会有提示。项目已入库不在进行提示。）</p>";
//			String deleteStr5 = "<p>档案员： <p>";
			if (!StringUtils.isEmpty(staffProject) && !StringUtils.isEmpty(leaderProject)
					&& !StringUtils.isEmpty(auditProject)) {
				f(deleteStr + deleteStr1 + deleteStr2 + deleteStr3);
				return;
			} else if (!StringUtils.isEmpty(staffProject) && !StringUtils.isEmpty(auditProject)) {
				f(deleteStr + deleteStr2 + deleteStr3);
				return;
			} else if (!StringUtils.isEmpty(auditProject)) {
				f(deleteStr + deleteStr3);
				return;
			} else if (!StringUtils.isEmpty(staffProject)) {
				f(deleteStr + deleteStr2);
				return;
			}

//			删除
			Q deletePeople = new Q(" DELETE FROM Mh_Resource WHERE Member = ? and Company = ?", memberId,
					Company);
			trans.add(deletePeople);
			// 关联删除工程师库
			Q dt = new Q("select * from eao_engineer_member where Company = ?", Company);
			dt.and().in(" Member ", memberId);
			DataTable dataTable = dt.fetch();
			if (dataTable.getRowCount() != 0) {
				for (DataRow dataRow : dataTable) {
					EaoEngineerMember engineer = new EaoEngineerMember();
					engineer.setValue(dataRow);
					trans.delete(engineer);
				}

			}
			Q dt1 = new Q("select * from eao_is_engineer where ");
			dt1.in(" Member ", memberId);
			DataTable table = dt1.fetch();
			if (table.getRowCount() != 0) {
				for (DataRow dataRow : table) {
					EaoIsEngineer engineer = new EaoIsEngineer();
					engineer.setValue(dataRow);
					trans.delete(engineer);
				}

			}
			Q dt2 = new Q("select * from eao_project_staff where StaffName = ? ", memberId);
			dt2.and().in("ProjectId", listProjectId);
			DataTable projectStaff = dt2.fetch();
			if (projectStaff.getRowCount() != 0) {
				for (DataRow dataRow : projectStaff) {
					EaoProjectStaff entity = new EaoProjectStaff();
					entity.setValue(dataRow);
					trans.delete(entity);
				}
			}
			if (trans.commit()) {
				s("删除成功");
			} else {
				f("删除失败");
			}
		} else {
			f("参数不能为空");
		}

	}

	@Priv(loginType = LoginType.ALL)
	public void findWithTime(CommonDataGridAction dga) {
		String sortField = $V("sortField");
		String sortOrder = $V("sortOrder");
		String CompanyInfo = $V("CompanyInfo");
		String ProjectName = $V("ProjectName");
		String StartTime = $V("StartTime");
		String EndTime = $V("EndTime");
		String ProjectType = $V("ProjectType");
		Q q = new Q(
				"select epi.Id,epi.Company,epi.ProjectName,epi.ReportType,epi.PerjectTime,epi.EntTime,epi.PerjectType,em.Mobile,em.RealName from eao_project_info epi left join Ec_Member em on epi.CreatePerson = em.ID  where 1 = 1 and epi.IsHide = '0' ");
		if (StringUtil.isNotEmpty(CompanyInfo)) {
			q.append(
					" and epi.Company in (select em.ID from ec_member em left join crm_company cc on em.ID = cc.ID where em.Mobile like ? or cc.FullName like ?)",
					"%" + CompanyInfo + "%", "%" + CompanyInfo + "%");
		}
		if (StringUtil.isNotEmpty(ProjectName)) {
			q.append(" and epi.ProjectName like ?", "%" + ProjectName + "%");
		}
		if (StringUtil.isNotEmpty(StartTime)) {
			q.and().append(" DATE_FORMAT(PerjectTime,'%Y-%m-%d')>=?", StartTime);
		}
		if (StringUtil.isNotEmpty(EndTime)) {
			q.and().append(" DATE_FORMAT(PerjectTime,'%Y-%m-%d')<=?", EndTime);
		}
		if (StringUtil.isNotEmpty(ProjectType)) {
			String[] arr = ProjectType.split(",");
			q.and().in("PerjectType", arr);
		}
		if (StringUtil.isNotEmpty(sortField) && StringUtil.isNotEmpty(sortOrder)) {
			q.orderby(sortField + " " + sortOrder);
		} else {
			q.orderby("epi.PerjectTime desc");
		}
		DataTable dt = q.fetch(dga);
		if (dt.getRowCount() > 0) {
			dt.insertColumn("CompanyName");
			dt.insertColumn("CompanyMobile");
			Set<Long> companyIds = new HashSet<>();
			for (DataRow dataRow : dt) {
				companyIds.add(dataRow.getLong("Company"));
			}
			DataTable companyName = new Q(
					"select cc.ID,cc.FullName,em.Mobile from crm_company cc left join ec_member em on cc.ID = em.ID where 1 = 1")
							.and().in("cc.ID", companyIds).fetch();
			for (DataRow project : dt) {
				String type = ProcedureUtils.projectType(project.getInt("PerjectType"));
				project.set("PerjectType", type);
				for (DataRow company : companyName) {
					if (project.getLong("Company") == company.getLong("ID")) {
						project.set("CompanyName", company.getString("FullName"));
						project.set("CompanyMobile", company.getString("Mobile"));
					}
				}
			}
			dga.bindData(dt);
			dga.setTotal(q);
		} else {
			$F("没有数据");
		}
	}

	/**
	 * 查询关于我们
	 */
	@Priv(loginType = LoginType.ALL)
	public void selectMe(CommonDataGridAction dga) {
		Q q1 = new Q(
				"select m.Id as Qid,m.EnterpriseName,m.Address,m.Phone,m.URL,v.Id as Bid,v.Version,v.VersionExplain,v.SoftwareUserAgreement,w.Id as Wid,w.Title,w.Content from eao_mechanism_info as m,eao_version as v,eao_wechat_public_address as w ORDER BY v.Version desc limit 1");
		dga.bindData(q1);
	}

	/**
	 * 查询账号数量
	 */
	@Priv(loginType = LoginType.ALL)
	public void findNumberEcMember() {
		int oneNum = 0;
		int twoNum = 0;
		int projectNum = 0;
		Q qOne = new Q("select count(em.Id) as Num from ec_member as em join ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%' where em.Type = 'Ent'");// 一级账号数量
		Q qTwo = new Q("select count(mr.Member) as Num from Mh_Resource as mr join ahj_member_remark as amr on amr.ID = mr.Member and amr.RemarkMember like '%eao%' where Company in(select ID from crm_company)");// 有机构信息的二级账号数量
		Q qThree = new Q("select count(1) as Num from eao_project_info");// 项目数量
		DataTable dtOne = qOne.fetch();
		for (DataRow dataRow : dtOne) {
			oneNum = dataRow.getInt("Num");
		}
		DataTable dtTwo = qTwo.fetch();
		for (DataRow dataRow : dtTwo) {
			twoNum = dataRow.getInt("Num");
		}
		DataTable dtThree = qThree.fetch();
		for (DataRow dataRow : dtThree) {
			projectNum = dataRow.getInt("Num");
		}
		if (dtOne.getRowCount() != 0) {
			this.Response.put("data", oneNum);
		} else {
			$F("查询一级账户失败");
		}
		if (dtTwo.getRowCount() != 0) {
			this.Response.put("data2", twoNum);
		} else {
			$F("查询二级账户失败");
		}
		if (dtThree.getRowCount() != 0) {
			this.Response.put("data3", projectNum);
		} else {
			$F("查询项目数量失败");
		}

	}
}
